using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Aces4All.Logger
{
    /// <summary>
    /// Database log
    /// </summary>
    public class DbLog : ILog
    {
        #region sql constants

        private const string SQL_CREATE_TABLE = 
            "CREATE TABLE {0}" +
            "(" + 
            "TimeSamp nchar(16) NOT NULL, " +
            "EntryType nvarchar(24) NULL, " +
            "ComputerName nvarchar(50) NULL, " +
            "Source nvarchar(50) NULL, " +
            "Message text NULL" + 
            ")\n" +
            "ON [PRIMARY]\n" +
            "TEXTIMAGE_ON [PRIMARY]";        
        private const string SQL_INSERT = "INSERT INTO {0}" +
            " (TimeStamp, EntryType, ComputerName, Source, Message)" +
            " values ({1})";
        private const string SQL_CONNECTION = "Data Source={0};" +
            "Initial Catalog={1};Integrated Security=True;Pooling=False";

        #endregion

        #region private fields

        private OleDbConnection m_Connection;

        #endregion

        #region public constructors/destructors

        /// <summary>
        /// Creates instance of DbLog
        /// </summary>
        /// <param name="connectionString">
        /// Logging DB connection string
        /// </param>
        /// <param name="sourceApplication">
        /// Name of application making log entries
        /// </param>
        /// <param name="dbTableName">
        /// Name of DB table log entries are being written to
        /// </param>
        public DbLog(string connectionString, string sourceApplication, 
            string dbTableName)
        {
            this.m_Log = connectionString;
            this.m_Source = sourceApplication;
            this.m_DbTableName = dbTableName;

            this.m_Connection = 
                new OleDbConnection(this.m_Log);
            this.m_DbServer = this.m_Connection.DataSource;
            this.m_DbName = this.m_Connection.Database;

            if (!this.TableExists())
            {
                this.CreateTable();
            }
        }

        /// <summary>
        /// Creates instance of DbLog
        /// </summary>
        /// <param name="dbServer">
        /// Name of DB servers log entries are being written to
        /// </param>
        /// <param name="databaseName">
        /// Name of DB log entries are being written to
        /// </param>
        /// <param name="sourceApplication">
        /// Name of application making log entries
        /// </param>
        /// <param name="dbTableName">
        /// Name of DB table log entries are being written to
        /// </param>
        private DbLog(string dbServer, string databaseName, 
            string sourceApplication, string dbTableName)
        {
            this.m_DbServer = dbServer;
            this.m_DbName = databaseName;
            this.m_Source = sourceApplication;
            this.m_DbTableName = dbTableName;

            this.m_Log = String.Format(SQL_CONNECTION, 
                this.m_DbServer, this.m_DbName);            
            this.m_Connection = new OleDbConnection(this.m_Log);
            this.m_Log = this.m_Connection.ConnectionString;

            if (!this.TableExists())
            {
                this.CreateTable();
            }
        }

        /// <summary>
        /// Releases all resources owned by DbLog
        /// </summary>
        ~DbLog()
        {
            if (this.m_Connection.State != ConnectionState.Closed)
            {
                this.m_Connection.Close();
            }
            this.m_Connection.Dispose();
        }

        #endregion

        #region public properties

        private string m_DbTableName;
        /// <summary>
        /// Gets the name of the table log entries are written to
        /// </summary>
        public string DbTableName
        {
            get
            {
                return this.m_DbTableName;
            }
        }

        private string m_Log;
        /// <summary>
        /// Gets connection string of logging DB
        /// </summary>
        /// <remarks></remarks>
        public string Log
        {
            get 
            {
                return this.m_Log;            
            }
        }

        private string m_Source;
        /// <summary>
        /// Gets source application making log entries
        /// </summary>
        public string Source
        {
            get
            {
                return this.m_Source;
            }
        }

        private string m_DbServer;
        /// <summary>
        /// Gets the name of the server logging database is located on.
        /// </summary>
        public string DbServer
        {
            get
            {
                return this.m_DbServer;
            }
        }

        private string m_DbName;
        /// <summary>
        /// Gets the name of logging database.
        /// </summary>
        public string DbName
        {
            get
            {
                return this.m_DbName;
            }
        }

        #endregion

        #region public methods

        /// <summary>
        /// Writes log entry to database
        /// </summary>
        /// <param name="entry">Log entry to write to database</param>
        public void WriteEntry(LogEntry entry)
        {
            string insertValues;
            insertValues = String.Join(",", entry.GetValues());
            string sqlInsetCmd = String.Format(SQL_INSERT,
                this.m_DbTableName, insertValues);
            OleDbCommand insertCommand = this.m_Connection.CreateCommand();
            insertCommand.CommandText = sqlInsetCmd;
            insertCommand.CommandType = CommandType.Text;

            try
            {
                this.m_Connection.Open();
                insertCommand.ExecuteNonQuery();
                this.m_Connection.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        #endregion

        #region private methods

        /// <summary>
        /// Creates database table for log entries
        /// </summary>
        private void CreateTable()
        {
            string sqlCreateTable = String.Format(SQL_CREATE_TABLE, 
                this.m_DbTableName);

            OleDbCommand createCommand = this.m_Connection.CreateCommand();
            createCommand.CommandText = sqlCreateTable;
            createCommand.CommandType = CommandType.Text;

            try
            {
                createCommand.Connection.Open();
                createCommand.ExecuteNonQuery();
                createCommand.Connection.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        /// <summary>
        /// Tests if database table for log entries exists
        /// </summary>
        /// <returns>Returns true if table exists</returns>
        private bool TableExists()
        {
            string selectCommand = String.Format("IF EXIXTS (SELECT 1 FROM " +
                "INFORMATION_SCHEMA.TABLES " +
                "WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='{0}') " +
                "SELECT TRUE ELSE SELECT FALSE", this.m_DbTableName);
            
            OleDbCommand existsCommand = this.m_Connection.CreateCommand();
            existsCommand.CommandText = selectCommand;
            existsCommand.CommandType = CommandType.Text;
            bool exists;
            exists = false;

            try
            {
                this.m_Connection.Open();
                exists = (bool)existsCommand.ExecuteScalar();
                this.m_Connection.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return exists;
        }

        #endregion
    }
}
